set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


with tmp as (
    select 
         lastest.in_from_code
        ,date_add('{{ execution_date | cst_ds }}',1) as update_date
        ,nvl(lastest.in_from_provider_code,shift_start.province_code) as in_from_provider_code
        ,base.in_from_city_code
        ,base.in_from_area_code
        ,nvl(lastest.out_to_provider_code,shift_end.province_code)as out_to_provider_code
        ,base.out_to_city_code
        ,base.out_to_area_code
        ,lastest.out_to_code
        ,lastest.in_branch_id
        ,lastest.main_id
        ,lastest.out_branch_id
        ,lastest.in_collect_code
        ,nvl(lastest.in_to_code,shift_start.center_code) as in_to_code
        ,nvl(lastest.start_center_code,shift_start.center_code) as start_center_code
        ,lastest.search_type as search_type
        ,lastest.start_network_code
        ,nvl(lastest.in_from_regional_code,shift_start.region_code) as in_from_regional_code
        ,nvl(lastest.in_from_regional_desc,shift_start.region_name) as in_from_regional_desc
        ,nvl(lastest.in_from_financial_center_code,shift_start.agent_code) as in_from_financial_center_code
        ,nvl(lastest.in_from_financial_center_desc,shift_start.agent_name)as in_from_financial_center_desc
        ,nvl(lastest.in_from_provider_desc,shift_start.province_name) as in_from_provider_desc
        ,base.in_from_city_desc
        ,base.in_from_area_desc
        ,lastest.in_collect_name
        ,lastest.in_from_name
        ,nvl(lastest.in_to_name,shift_start.center_name) as in_to_name
        ,lastest.in_edge_latest_warehousing
        ,lastest.in_edge_planned_departure
        ,lastest.in_edge_planned_arrival
        ,lastest.in_edge2_planned_departure
        ,lastest.in_edge2_planned_arrival
        ,lastest.branch_in_span_days
        ,lastest.branch_in_total_time
        ,lastest.is_main_route
        ,lastest.transfer_type
        ,lastest.num_of_transfer
        ,lastest.whole_route
        ,lastest.e1_center_flow
        ,lastest.e1_line_code
        ,lastest.e1_planned_departure
        ,lastest.e1_runtime
        ,lastest.e1_planned_arrival
        ,lastest.e1_e2_stop_time
        ,lastest.e2_center_flow
        ,lastest.e2_line_code
        ,lastest.e2_planned_departure
        ,lastest.e2_runtime
        ,lastest.e2_planned_arrival
        ,lastest.e2_e3_stop_time
        ,lastest.e3_center_flow
        ,lastest.e3_line_code
        ,lastest.e3_planned_departure
        ,lastest.e3_runtime
        ,lastest.e3_planned_arrival
        ,lastest.e3_e4_stop_time
        ,lastest.e4_center_flow
        ,lastest.e4_line_code
        ,lastest.e4_planned_departure
        ,lastest.e4_runtime
        ,lastest.e4_planned_arrival
        ,lastest.e4_e5_stop_time
        ,lastest.e5_center_flow
        ,lastest.e5_line_code
        ,lastest.e5_planned_departure
        ,lastest.e5_runtime
        ,lastest.e5_planned_arrival
        ,lastest.e5_e6_stop_time
        ,lastest.e6_center_flow
        ,lastest.e6_line_code
        ,lastest.e6_planned_departure
        ,lastest.e6_runtime
        ,lastest.e6_planned_arrival
        ,lastest.e6_e7_stop_time
        ,lastest.e1_end_code
        ,lastest.e2_end_code
        ,lastest.e3_end_code
        ,lastest.e4_end_code
        ,lastest.e5_end_code
        ,lastest.e6_end_code
        ,lastest.transfer_total_span_days
        ,lastest.transfer_total_time_use
        ,lastest.main_total_span_days
        ,lastest.main_total_time_use
        ,nvl(lastest.end_center,shift_end.center_name) as end_center
        ,nvl(lastest.end_center_code,shift_end.center_code) as end_center_code
        ,nvl(lastest.start_center,shift_start.center_name) as start_center
        ,nvl(lastest.out_from_code,shift_end.center_code) as out_from_code
        ,lastest.out_collect_code
        ,nvl(lastest.out_to_regional_desc,shift_end.region_name) as out_to_regional_desc
        ,nvl(lastest.out_to_regional_code,shift_end.region_code)  as out_to_regional_code
        ,nvl(lastest.out_to_financial_center_desc,shift_end.agent_name) as out_to_financial_center_desc
        ,nvl(lastest.out_to_financial_center_code,shift_end.agent_code) as out_to_financial_center_code
        ,nvl(lastest.out_to_provider_desc,shift_end.province_name) as out_to_provider_desc
        ,base.out_to_city_desc
        ,base.out_to_area_desc
        ,lastest.out_collect_name
        ,lastest.out_to_name
        ,nvl(lastest.out_from_name,shift_end.center_name) as out_from_name
        ,lastest.out_has_collect
        ,lastest.out_edge_planned_departure
        ,lastest.out_edge_planned_arrival_time
        ,lastest.out_edge2_planned_departure
        ,lastest.out_edge2_planned_arrival_time
        ,lastest.out_edge2_latest_warehouse_time
        ,lastest.out_edge_latest_warehouse_time
        ,lastest.out_edge_span_days_sign
        ,lastest.out_edge2_span_days_sign
        ,lastest.out_edge_deadline_sign_time
        ,lastest.out_edge2_deadline_sign_time
        ,lastest.branch_out_span_days
        ,lastest.branch_out_total_time
        ,lastest.effective_date
        ,lastest.expiration_date
        ,lastest.branch_in_center_stop_time
        ,lastest.center_branch_out_stop_time
        ,lastest.branch_in_center_span_days
        ,lastest.center_branch_out_span_days
        ,lastest.is_circuitous
        ,lastest.out_edge_span_days_arrive
        ,lastest.out_edge2_span_days_arrive
        ,lastest.in_nodes
        ,lastest.out_nodes
        ,lastest.total_days_use
        ,lastest.total_time_use
        ,lastest.total_days_t
        ,lastest.total_nodes
        ,lastest.working_days
        ,lastest.extra_in_collect_code
        ,lastest.extra_in_collect_name
        ,lastest.extra_in_line_name
        ,lastest.extra_in_latest_warehousing
        ,lastest.extra_in_planned_departure
        ,lastest.extra_in_planned_arrival
        ,lastest.extra_out_collect_code
        ,lastest.extra_out_collect_name
        ,lastest.extra_out_line_name
        ,lastest.extra_out_latest_warehousing
        ,lastest.extra_out_planned_departure
        ,lastest.extra_out_planned_arrival
        ,lastest.in_network_line_name
        ,lastest.in_collect_line_name
        ,lastest.out_network_line_name
        ,lastest.out_collect_line_name
        ,lastest.town_plus_time
        ,lastest.branch_in_ship_time
        ,lastest.branch_in_ship_span_days
        ,lastest.branch_out_ship_time
        ,lastest.branch_out_ship_span_days
        ,lastest.all_line_name
        ,lastest.out_town_plus_time
        ,lastest.in_edge_run_time
        ,lastest.in_edge_span_days
        ,lastest.in_edge2_run_time
        ,lastest.in_edge2_span_days
        ,lastest.in_edge3_run_time
        ,lastest.in_edge3_span_days
        ,lastest.out_edge_run_time
        ,lastest.out_edge_span_days
        ,lastest.out_edge2_run_time
        ,lastest.out_edge2_span_days
        ,lastest.out_edge3_run_time
        ,lastest.out_edge3_span_days
        ,lastest.e1_span_days
        ,lastest.e2_span_days
        ,lastest.e3_span_days
        ,lastest.e4_span_days
        ,lastest.e5_span_days
        ,lastest.e6_span_days
        ,lastest.delivery_time
        ,lastest.route_flow
        ,lastest.in_edge2_latest_warehousing
        ,lastest.e1_latest_warehousing
        ,lastest.e2_latest_warehousing
        ,lastest.e3_latest_warehousing
        ,lastest.e4_latest_arrival_time
        ,lastest.e5_latest_arrival_time
        ,lastest.e6_latest_arrival_time
        ,lastest.start_taking_shift
        ,lastest.end_send_shift
        ,lastest.warehouse_end_time
        ,lastest.reserve_1
        ,lastest.reserve_2
        ,lastest.route_contain_main_line
        ,lastest.direct_route_type
        ,lastest.in_line_day
        ,lastest.out_line_day
        ,lastest.in_ship_span_days
        ,lastest.in_manage_region_code
        ,lastest.in_manage_region_name
        ,lastest.out_manage_region_code
        ,lastest.out_manage_region_name
        ,base.is_main_city  --时效区县是否是主城区
        ,lastest.latest_warehousing_time_quantum as latest_warehousing_time_quantum --20221210新增揽收时间段
        ,base.is_main_city_start --始发城市是否主城区
        ,base.is_main_city_end --目的城市是否主城区
        ,lastest.branch_in_total_all_time
        ,lastest.main_total_all_time
        ,lastest.branch_out_total_all_time
    from jms_dm.dm_route_area_effect_detail_dt base
    left join (
        select *
        from jms_dm.dm_route_lastest_base_dt
        where dt = '{{ execution_date | cst_ds }}'
    ) lastest on lastest.in_from_area_code=nvl(base.in_from_aging_area_code,base.in_from_area_code) --优先用时效区县,次用兜底路由区县
             and lastest.out_to_area_code=nvl(base.out_to_aging_area_code,base.out_to_area_code) --优先用时效区县,次用兜底路由区县
    left join jms_dim.dim_yl_tms_bulk_cargo_shift_base shift_start --关联散货班次，补充基础资料为空场景
       on shift_start.area_code = base.in_from_area_code --始发区县
    left join jms_dim.dim_yl_tms_bulk_cargo_shift_base shift_end
       on shift_end.area_code = base.out_to_area_code --目的区县
    where base.dt = '{{ execution_date | cst_ds }}'
    group by lastest.in_from_code
            ,date_add('{{ execution_date | cst_ds }}',1)
            ,nvl(lastest.in_from_provider_code,shift_start.province_code)
            ,base.in_from_city_code
            ,base.in_from_area_code
            ,nvl(lastest.out_to_provider_code,shift_end.province_code)
            ,base.out_to_city_code
            ,base.out_to_area_code
            ,lastest.out_to_code
            ,lastest.in_branch_id
            ,lastest.main_id
            ,lastest.out_branch_id
            ,lastest.in_collect_code
            ,nvl(lastest.in_to_code,shift_start.center_code)
            ,nvl(lastest.start_center_code,shift_start.center_code)
            ,lastest.search_type
            ,lastest.start_network_code
            ,nvl(lastest.in_from_regional_code,shift_start.region_code)
            ,nvl(lastest.in_from_regional_desc,shift_start.region_name)
            ,nvl(lastest.in_from_financial_center_code,shift_start.agent_code)
            ,nvl(lastest.in_from_financial_center_desc,shift_start.agent_name)
            ,nvl(lastest.in_from_provider_desc,shift_start.province_name)
            ,base.in_from_city_desc
            ,base.in_from_area_desc
            ,lastest.in_collect_name
            ,lastest.in_from_name
            ,nvl(lastest.in_to_name,shift_start.center_name)
            ,lastest.in_edge_latest_warehousing
            ,lastest.in_edge_planned_departure
            ,lastest.in_edge_planned_arrival
            ,lastest.in_edge2_planned_departure
            ,lastest.in_edge2_planned_arrival
            ,lastest.branch_in_span_days
            ,lastest.branch_in_total_time
            ,lastest.is_main_route
            ,lastest.transfer_type
            ,lastest.num_of_transfer
            ,lastest.whole_route
            ,lastest.e1_center_flow
            ,lastest.e1_line_code
            ,lastest.e1_planned_departure
            ,lastest.e1_runtime
            ,lastest.e1_planned_arrival
            ,lastest.e1_e2_stop_time
            ,lastest.e2_center_flow
            ,lastest.e2_line_code
            ,lastest.e2_planned_departure
            ,lastest.e2_runtime
            ,lastest.e2_planned_arrival
            ,lastest.e2_e3_stop_time
            ,lastest.e3_center_flow
            ,lastest.e3_line_code
            ,lastest.e3_planned_departure
            ,lastest.e3_runtime
            ,lastest.e3_planned_arrival
            ,lastest.e3_e4_stop_time
            ,lastest.e4_center_flow
            ,lastest.e4_line_code
            ,lastest.e4_planned_departure
            ,lastest.e4_runtime
            ,lastest.e4_planned_arrival
            ,lastest.e4_e5_stop_time
            ,lastest.e5_center_flow
            ,lastest.e5_line_code
            ,lastest.e5_planned_departure
            ,lastest.e5_runtime
            ,lastest.e5_planned_arrival
            ,lastest.e5_e6_stop_time
            ,lastest.e6_center_flow
            ,lastest.e6_line_code
            ,lastest.e6_planned_departure
            ,lastest.e6_runtime
            ,lastest.e6_planned_arrival
            ,lastest.e6_e7_stop_time
            ,lastest.e1_end_code
            ,lastest.e2_end_code
            ,lastest.e3_end_code
            ,lastest.e4_end_code
            ,lastest.e5_end_code
            ,lastest.e6_end_code
            ,lastest.transfer_total_span_days
            ,lastest.transfer_total_time_use
            ,lastest.main_total_span_days
            ,lastest.main_total_time_use
            ,nvl(lastest.end_center,shift_end.center_name)
            ,nvl(lastest.end_center_code,shift_end.center_code)
            ,nvl(lastest.start_center,shift_start.center_name)
            ,nvl(lastest.out_from_code,shift_end.center_code)
            ,lastest.out_collect_code
            ,nvl(lastest.out_to_regional_desc,shift_end.region_name)
            ,nvl(lastest.out_to_regional_code,shift_end.region_code)
            ,nvl(lastest.out_to_financial_center_desc,shift_end.agent_name)
            ,nvl(lastest.out_to_financial_center_code,shift_end.agent_code)
            ,nvl(lastest.out_to_provider_desc,shift_end.province_name)
            ,base.out_to_city_desc
            ,base.out_to_area_desc
            ,lastest.out_collect_name
            ,lastest.out_to_name
            ,nvl(lastest.out_from_name,shift_end.center_name)
            ,lastest.out_has_collect
            ,lastest.out_edge_planned_departure
            ,lastest.out_edge_planned_arrival_time
            ,lastest.out_edge2_planned_departure
            ,lastest.out_edge2_planned_arrival_time
            ,lastest.out_edge2_latest_warehouse_time
            ,lastest.out_edge_latest_warehouse_time
            ,lastest.out_edge_span_days_sign
            ,lastest.out_edge2_span_days_sign
            ,lastest.out_edge_deadline_sign_time
            ,lastest.out_edge2_deadline_sign_time
            ,lastest.branch_out_span_days
            ,lastest.branch_out_total_time
            ,lastest.effective_date
            ,lastest.expiration_date
            ,lastest.branch_in_center_stop_time
            ,lastest.center_branch_out_stop_time
            ,lastest.branch_in_center_span_days
            ,lastest.center_branch_out_span_days
            ,lastest.is_circuitous
            ,lastest.out_edge_span_days_arrive
            ,lastest.out_edge2_span_days_arrive
            ,lastest.in_nodes
            ,lastest.out_nodes
            ,lastest.total_days_use
            ,lastest.total_time_use
            ,lastest.total_days_t
            ,lastest.total_nodes
            ,lastest.working_days
            ,lastest.extra_in_collect_code
            ,lastest.extra_in_collect_name
            ,lastest.extra_in_line_name
            ,lastest.extra_in_latest_warehousing
            ,lastest.extra_in_planned_departure
            ,lastest.extra_in_planned_arrival
            ,lastest.extra_out_collect_code
            ,lastest.extra_out_collect_name
            ,lastest.extra_out_line_name
            ,lastest.extra_out_latest_warehousing
            ,lastest.extra_out_planned_departure
            ,lastest.extra_out_planned_arrival
            ,lastest.in_network_line_name
            ,lastest.in_collect_line_name
            ,lastest.out_network_line_name
            ,lastest.out_collect_line_name
            ,lastest.town_plus_time
            ,lastest.branch_in_ship_time
            ,lastest.branch_in_ship_span_days
            ,lastest.branch_out_ship_time
            ,lastest.branch_out_ship_span_days
            ,lastest.all_line_name
            ,lastest.out_town_plus_time
            ,lastest.in_edge_run_time
            ,lastest.in_edge_span_days
            ,lastest.in_edge2_run_time
            ,lastest.in_edge2_span_days
            ,lastest.in_edge3_run_time
            ,lastest.in_edge3_span_days
            ,lastest.out_edge_run_time
            ,lastest.out_edge_span_days
            ,lastest.out_edge2_run_time
            ,lastest.out_edge2_span_days
            ,lastest.out_edge3_run_time
            ,lastest.out_edge3_span_days
            ,lastest.e1_span_days
            ,lastest.e2_span_days
            ,lastest.e3_span_days
            ,lastest.e4_span_days
            ,lastest.e5_span_days
            ,lastest.e6_span_days
            ,lastest.delivery_time
            ,lastest.route_flow
            ,lastest.in_edge2_latest_warehousing
            ,lastest.e1_latest_warehousing
            ,lastest.e2_latest_warehousing
            ,lastest.e3_latest_warehousing
            ,lastest.e4_latest_arrival_time
            ,lastest.e5_latest_arrival_time
            ,lastest.e6_latest_arrival_time
            ,lastest.start_taking_shift
            ,lastest.end_send_shift
            ,lastest.warehouse_end_time
            ,lastest.reserve_1
            ,lastest.reserve_2
            ,lastest.route_contain_main_line
            ,lastest.direct_route_type
            ,lastest.in_line_day
            ,lastest.out_line_day
            ,lastest.in_ship_span_days
            ,lastest.in_manage_region_code
            ,lastest.in_manage_region_name
            ,lastest.out_manage_region_code
            ,lastest.out_manage_region_name
            ,base.is_main_city  --时效区县是否是主城区
            ,lastest.latest_warehousing_time_quantum
            ,base.is_main_city_start --始发城市是否主城区
            ,base.is_main_city_end --目的城市是否主城区
            ,lastest.branch_in_total_all_time
            ,lastest.main_total_all_time
            ,lastest.branch_out_total_all_time
)




insert overwrite table jms_tmp.dm_route_city_fastest_dt_tmp partition(dt = '{{ execution_date | cst_ds }}')
select
     base.*
    ,cn_effe.platform_code  as platform_code_cn
    ,cn_effe.platform       as platform_cn
    ,cn_effe.times          as effective_times_cn
    ,zt_effe.platform_code  as platform_code_zt
    ,zt_effe.platform       as platform_zt
    ,zt_effe.times          as effective_times_zt
    ,sf_effe.platform_code  as platform_code_sf
    ,sf_effe.platform       as platform_sf
    ,sf_effe.times          as effective_times_sf
    ,yt_effe.platform_code  as platform_code_yt
    ,yt_effe.platform       as platform_yt
    ,yt_effe.times          as effective_times_yt
    ,yd_effe.platform_code  as platform_code_yd
    ,yd_effe.platform       as platform_yd
    ,yd_effe.times          as effective_times_yd
    ,thd_effe.platform_code as platform_code_thd
    ,thd_effe.platform      as platform_thd
    ,thd_effe.times         as effective_times_thd
    ,zjs_effe.platform_code as platform_code_zjs
    ,zjs_effe.platform      as platform_zjs
    ,zjs_effe.times         as effective_times_zjs
    ,case when cn_effe.times < 1 then '当日达'
          when cn_effe.times < 2 then '次日达'
          when cn_effe.times < 3 then '隔日达'
          when cn_effe.times < 4 then '三日达'
          when cn_effe.times < 5 then '四日达'
          when cn_effe.times < 6 then '五日达'
          else if(cn_effe.times is not null,'五日达以上',null) end as total_days_use_cn
    ,case when zt_effe.times < 1 then '当日达'
          when zt_effe.times < 2 then '次日达'
          when zt_effe.times < 3 then '隔日达'
          when zt_effe.times < 4 then '三日达'
          when zt_effe.times < 5 then '四日达'
          when zt_effe.times < 6 then '五日达'
          else if(zt_effe.times is not null,'五日达以上',null) end as total_days_use_zt
    ,case when sf_effe.times < 1 then '当日达'
          when sf_effe.times < 2 then '次日达'
          when sf_effe.times < 3 then '隔日达'
          when sf_effe.times < 4 then '三日达'
          when sf_effe.times < 5 then '四日达'
          when sf_effe.times < 6 then '五日达'
          else if(sf_effe.times is not null,'五日达以上',null) end as total_days_use_sf
    ,case when yt_effe.times < 1 then '当日达'
          when yt_effe.times < 2 then '次日达'
          when yt_effe.times < 3 then '隔日达'
          when yt_effe.times < 4 then '三日达'
          when yt_effe.times < 5 then '四日达'
          when yt_effe.times < 6 then '五日达'
          else if(yt_effe.times is not null,'五日达以上',null) end as total_days_use_yt
    ,case when yd_effe.times < 1 then '当日达'
          when yd_effe.times < 2 then '次日达'
          when yd_effe.times < 3 then '隔日达'
          when yd_effe.times < 4 then '三日达'
          when yd_effe.times < 5 then '四日达'
          when yd_effe.times < 6 then '五日达'
          else if(yd_effe.times is not null,'五日达以上',null) end as total_days_use_yd
    ,case when thd_effe.times < 1 then '当日达'
          when thd_effe.times < 2 then '次日达'
          when thd_effe.times < 3 then '隔日达'
          when thd_effe.times < 4 then '三日达'
          when thd_effe.times < 5 then '四日达'
          when thd_effe.times < 6 then '五日达'
          else if(thd_effe.times is not null,'五日达以上',null) end as total_days_use_thd
    ,case when zjs_effe.times < 1 then '当日达'
          when zjs_effe.times < 2 then '次日达'
          when zjs_effe.times < 3 then '隔日达'
          when zjs_effe.times < 4 then '三日达'
          when zjs_effe.times < 5 then '四日达'
          when zjs_effe.times < 6 then '五日达'
          else if(zjs_effe.times is not null,'五日达以上',null) end as total_days_use_zjs
    ,case when cn_effe.times < 1 then 'T+0'
          when cn_effe.times < 2 then 'T+1'
          when cn_effe.times < 3 THEN 'T+2'
          when cn_effe.times < 4 THEN 'T+3'
          when cn_effe.times < 5 THEN 'T+4'
          when cn_effe.times < 6 THEN 'T+5'
          else concat('T+',cn_effe.times)
          end as total_days_t_cn
    ,case when zt_effe.times < 1 then 'T+0'
          when zt_effe.times < 2 then 'T+1'
          when zt_effe.times < 3 THEN 'T+2'
          when zt_effe.times < 4 THEN 'T+3'
          when zt_effe.times < 5 THEN 'T+4'
          when zt_effe.times < 6 THEN 'T+5'
          else concat('T+',zt_effe.times)
          end as total_days_t_zt
    ,case when sf_effe.times < 1 then 'T+0'
          when sf_effe.times < 2 then 'T+1'
          when sf_effe.times < 3 THEN 'T+2'
          when sf_effe.times < 4 THEN 'T+3'
          when sf_effe.times < 5 THEN 'T+4'
          when sf_effe.times < 6 THEN 'T+5'
          else concat('T+',sf_effe.times)
          end as total_days_t_sf
    ,case when yt_effe.times < 1 then 'T+0'
          when yt_effe.times < 2 then 'T+1'
          when yt_effe.times < 3 THEN 'T+2'
          when yt_effe.times < 4 THEN 'T+3'
          when yt_effe.times < 5 THEN 'T+4'
          when yt_effe.times < 6 THEN 'T+5'
          else concat('T+',yt_effe.times)
          end as total_days_t_yt
    ,case when yd_effe.times < 1 then 'T+0'
          when yd_effe.times < 2 then 'T+1'
          when yd_effe.times < 3 THEN 'T+2'
          when yd_effe.times < 4 THEN 'T+3'
          when yd_effe.times < 5 THEN 'T+4'
          when yd_effe.times < 6 THEN 'T+5'
          else concat('T+',yd_effe.times)
          end as total_days_t_yd
    ,case when thd_effe.times < 1 then 'T+0'
          when thd_effe.times < 2 then 'T+1'
          when thd_effe.times < 3 THEN 'T+2'
          when thd_effe.times < 4 THEN 'T+3'
          when thd_effe.times < 5 THEN 'T+4'
          when thd_effe.times < 6 THEN 'T+5'
          else concat('T+',thd_effe.times)
          end as total_days_t_thd
    ,case when zjs_effe.times < 1 then 'T+0'
          when zjs_effe.times < 2 then 'T+1'
          when zjs_effe.times < 3 THEN 'T+2'
          when zjs_effe.times < 4 THEN 'T+3'
          when zjs_effe.times < 5 THEN 'T+4'
          when zjs_effe.times < 6 THEN 'T+5'
          else concat('T+',zjs_effe.times)
          end as total_days_t_zjs
from tmp base
left join(
    select *
     from jms_dim.dim_tab_rou_competitor_effective --同行时效配置表
     where platform_code = 1 --菜鸟
) cn_effe on cn_effe.sender_city_id = base.in_from_city_code
         and cn_effe.receiver_city_id = base.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 2 --中通
) zt_effe on zt_effe.sender_city_id = base.in_from_city_code
         and zt_effe.receiver_city_id = base.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 3 --顺丰
) sf_effe on sf_effe.sender_city_id = base.in_from_city_code
         and sf_effe.receiver_city_id = base.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 4 --圆通
) yt_effe on yt_effe.sender_city_id = base.in_from_city_code
         and yt_effe.receiver_city_id = base.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 5 --韵达
) yd_effe on yd_effe.sender_city_id = base.in_from_city_code
         and yd_effe.receiver_city_id = base.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 6 --桃花岛
) thd_effe on thd_effe.sender_city_id = base.in_from_city_code
          and thd_effe.receiver_city_id = base.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 7 --紫金山
) zjs_effe on zjs_effe.sender_city_id = base.in_from_city_code
          and zjs_effe.receiver_city_id = base.out_to_city_code
distribute by pmod(hash(rand()),80)
;



insert overwrite table jms_dm.dm_route_city_fastest_dt partition(dt = '{{ execution_date | cst_ds }}')
select
     a.in_from_code
    ,a.update_date
    ,a.in_from_provider_code
    ,a.in_from_city_code
    ,a.in_from_area_code
    ,a.out_to_provider_code
    ,a.out_to_city_code
    ,a.out_to_area_code
    ,a.out_to_code
    ,a.in_branch_id
    ,a.main_id
    ,a.out_branch_id
    ,a.in_collect_code
    ,a.in_to_code
    ,a.start_center_code
    ,a.search_type
    ,a.start_network_code
    ,a.in_from_regional_code
    ,a.in_from_regional_desc
    ,a.in_from_financial_center_code
    ,a.in_from_financial_center_desc
    ,a.in_from_provider_desc
    ,a.in_from_city_desc
    ,a.in_from_area_desc
    ,a.in_collect_name
    ,a.in_from_name
    ,a.in_to_name
    ,a.in_edge_latest_warehousing
    ,a.in_edge_planned_departure
    ,a.in_edge_planned_arrival
    ,a.in_edge2_planned_departure
    ,a.in_edge2_planned_arrival
    ,a.branch_in_span_days
    ,a.branch_in_total_time
    ,a.is_main_route
    ,a.transfer_type
    ,a.num_of_transfer
    ,a.whole_route
    ,a.e1_center_flow
    ,a.e1_line_code
    ,a.e1_planned_departure
    ,a.e1_runtime
    ,a.e1_planned_arrival
    ,a.e1_e2_stop_time
    ,a.e2_center_flow
    ,a.e2_line_code
    ,a.e2_planned_departure
    ,a.e2_runtime
    ,a.e2_planned_arrival
    ,a.e2_e3_stop_time
    ,a.e3_center_flow
    ,a.e3_line_code
    ,a.e3_planned_departure
    ,a.e3_runtime
    ,a.e3_planned_arrival
    ,a.e3_e4_stop_time
    ,a.e4_center_flow
    ,a.e4_line_code
    ,a.e4_planned_departure
    ,a.e4_runtime
    ,a.e4_planned_arrival
    ,a.e4_e5_stop_time
    ,a.e5_center_flow
    ,a.e5_line_code
    ,a.e5_planned_departure
    ,a.e5_runtime
    ,a.e5_planned_arrival
    ,a.e5_e6_stop_time
    ,a.e6_center_flow
    ,a.e6_line_code
    ,a.e6_planned_departure
    ,a.e6_runtime
    ,a.e6_planned_arrival
    ,a.e6_e7_stop_time
    ,a.e1_end_code
    ,a.e2_end_code
    ,a.e3_end_code
    ,a.e4_end_code
    ,a.e5_end_code
    ,a.e6_end_code
    ,a.transfer_total_span_days
    ,a.transfer_total_time_use
    ,a.main_total_span_days
    ,a.main_total_time_use
    ,a.end_center
    ,a.end_center_code
    ,a.start_center
    ,a.out_from_code
    ,a.out_collect_code
    ,a.out_to_regional_desc
    ,a.out_to_regional_code
    ,a.out_to_financial_center_desc
    ,a.out_to_financial_center_code
    ,a.out_to_provider_desc
    ,a.out_to_city_desc
    ,a.out_to_area_desc
    ,a.out_collect_name
    ,a.out_to_name
    ,a.out_from_name
    ,a.out_has_collect
    ,a.out_edge_planned_departure
    ,a.out_edge_planned_arrival_time
    ,a.out_edge2_planned_departure
    ,a.out_edge2_planned_arrival_time
    ,a.out_edge2_latest_warehouse_time
    ,a.out_edge_latest_warehouse_time
    ,a.out_edge_span_days_sign
    ,a.out_edge2_span_days_sign
    ,a.out_edge_deadline_sign_time
    ,a.out_edge2_deadline_sign_time
    ,a.branch_out_span_days
    ,a.branch_out_total_time
    ,a.effective_date
    ,a.expiration_date
    ,a.branch_in_center_stop_time
    ,a.center_branch_out_stop_time
    ,a.branch_in_center_span_days
    ,a.center_branch_out_span_days
    ,a.is_circuitous
    ,a.out_edge_span_days_arrive
    ,a.out_edge2_span_days_arrive
    ,a.in_nodes
    ,a.out_nodes
    ,a.total_days_use
    ,a.total_time_use
    ,a.total_days_t
    ,a.total_nodes
    ,a.working_days
    ,a.extra_in_collect_code
    ,a.extra_in_collect_name
    ,a.extra_in_line_name
    ,a.extra_in_latest_warehousing
    ,a.extra_in_planned_departure
    ,a.extra_in_planned_arrival
    ,a.extra_out_collect_code
    ,a.extra_out_collect_name
    ,a.extra_out_line_name
    ,a.extra_out_latest_warehousing
    ,a.extra_out_planned_departure
    ,a.extra_out_planned_arrival
    ,a.in_network_line_name
    ,a.in_collect_line_name
    ,a.out_network_line_name
    ,a.out_collect_line_name
    ,a.town_plus_time
    ,a.branch_in_ship_time
    ,a.branch_in_ship_span_days
    ,a.branch_out_ship_time
    ,a.branch_out_ship_span_days
    ,a.all_line_name
    ,a.out_town_plus_time
    ,a.in_edge_run_time
    ,a.in_edge_span_days
    ,a.in_edge2_run_time
    ,a.in_edge2_span_days
    ,a.in_edge3_run_time
    ,a.in_edge3_span_days
    ,a.out_edge_run_time
    ,a.out_edge_span_days
    ,a.out_edge2_run_time
    ,a.out_edge2_span_days
    ,a.out_edge3_run_time
    ,a.out_edge3_span_days
    ,a.e1_span_days
    ,a.e2_span_days
    ,a.e3_span_days
    ,a.e4_span_days
    ,a.e5_span_days
    ,a.e6_span_days
    ,a.delivery_time
    ,a.route_flow
    ,a.in_edge2_latest_warehousing
    ,a.e1_latest_warehousing
    ,a.e2_latest_warehousing
    ,a.e3_latest_warehousing
    ,a.e4_latest_arrival_time
    ,a.e5_latest_arrival_time
    ,a.e6_latest_arrival_time
    ,a.start_taking_shift
    ,a.end_send_shift
    ,a.warehouse_end_time
    ,a.reserve_1
    ,a.reserve_2
    ,a.route_contain_main_line
    ,a.direct_route_type
    ,a.in_line_day
    ,a.out_line_day
    ,a.in_ship_span_days
    ,a.in_manage_region_code
    ,a.in_manage_region_name
    ,a.out_manage_region_code
    ,a.out_manage_region_name
    ,a.is_main_city
    ,a.latest_warehousing_time_quantum
    ,a.platform_code_cn
    ,a.platform_cn
    ,a.effective_times_cn
    ,a.platform_code_zt
    ,a.platform_zt
    ,a.effective_times_zt
    ,a.platform_code_sf
    ,a.platform_sf
    ,a.effective_times_sf
    ,a.platform_code_yt
    ,a.platform_yt
    ,a.effective_times_yt
    ,a.platform_code_yd
    ,a.platform_yd
    ,a.effective_times_yd
    ,a.platform_code_thd
    ,a.platform_thd
    ,a.effective_times_thd
    ,a.platform_code_zjs
    ,a.platform_zjs
    ,a.effective_times_zjs
    ,a.total_days_use_cn
    ,a.total_days_use_zt
    ,a.total_days_use_sf
    ,a.total_days_use_yt
    ,a.total_days_use_yd
    ,a.total_days_use_thd
    ,a.total_days_use_zjs
    ,a.total_days_t_cn
    ,a.total_days_t_zt
    ,a.total_days_t_sf
    ,a.total_days_t_yt
    ,a.total_days_t_yd
    ,a.total_days_t_thd
    ,a.total_days_t_zjs
    ,case when h.dt is not null then 1 else 0 end as is_city_fastest            --城市-城市最快标识
    ,case when g.dt is not null then 1 else 0 end as is_city_area_fastest       --城市-区县最快标识
    ,case when e.dt is not null then 1 else 0 end as is_area_city_fastest       --区县-城市最快标识
    ,case when f.dt is not null then 1 else 0 end as is_city_network_fastest    --城市-网点最快标识
    ,case when c.dt is not null then 1 else 0 end as is_network_city_fastest    --网点-城市最快标识
    ,case when i.dt is not null then 1 else 0 end as is_area_fastest            --区县-区县最快标识
    ,case when d.dt is not null then 1 else 0 end as is_area_network_fastest    --区县-网点最快标识
    ,case when b.dt is not null then 1 else 0 end as is_network_area_fastest    --网点-区县最快标识
    ,a.branch_in_total_all_time
    ,a.main_total_all_time
    ,a.branch_out_total_all_time
from (                                
    select  
        *
    from jms_tmp.dm_route_city_fastest_dt_tmp
    where dt = '{{ execution_date | cst_ds }}'
) a
--网点-区县
left join (
    select * 
    from ( 
        select
            lastest.*
           ,row_number() over(partition by in_from_code,out_to_area_code order by search_type asc,total_time_use asc,in_edge_planned_departure desc,in_edge2_planned_departure desc,
                                           extra_in_planned_departure desc,e1_planned_departure desc,e2_planned_departure desc,e3_planned_departure desc,e4_planned_departure desc,
                                           e5_planned_departure desc,e6_planned_departure desc,out_edge_planned_departure desc,extra_out_planned_departure desc,out_edge2_planned_departure desc) as rn --区县最快
        from jms_tmp.dm_route_city_fastest_dt_tmp lastest
        where dt = '{{ execution_date | cst_ds }}'
    ) a where a.rn = 1
) b on a.in_from_code = b.in_from_code
   and a.out_to_code = b.out_to_code
   and a.out_to_area_code = b.out_to_area_code
--网点-城市
left join (
    select *
    from ( 
        select
            lastest.*
           ,row_number() over(partition by in_from_code,out_to_city_code order by search_type asc,total_time_use asc,in_edge_planned_departure desc,in_edge2_planned_departure desc,
                                           extra_in_planned_departure desc,e1_planned_departure desc,e2_planned_departure desc,e3_planned_departure desc,e4_planned_departure desc,
                                           e5_planned_departure desc,e6_planned_departure desc,out_edge_planned_departure desc,extra_out_planned_departure desc,out_edge2_planned_departure desc) as rn --区县最快
        from jms_tmp.dm_route_city_fastest_dt_tmp  lastest --兜底路由
        where dt = '{{ execution_date | cst_ds }}'
        and is_main_city_end = 1 
    ) a where a.rn = 1 
) c on a.in_from_code = c.in_from_code
   and a.out_to_code = c.out_to_code
   and a.out_to_city_code = c.out_to_city_code
--区县-网点
left join (
    select * 
    from ( 
        select
            lastest.*
           ,row_number() over(partition by in_from_area_code,out_to_code order by search_type asc,total_time_use asc,in_edge_planned_departure desc,in_edge2_planned_departure desc,
                                           extra_in_planned_departure desc,e1_planned_departure desc,e2_planned_departure desc,e3_planned_departure desc,e4_planned_departure desc,
                                           e5_planned_departure desc,e6_planned_departure desc,out_edge_planned_departure desc,extra_out_planned_departure desc,out_edge2_planned_departure desc) as rn --区县最快
        from jms_tmp.dm_route_city_fastest_dt_tmp lastest
        where dt = '{{ execution_date | cst_ds }}'
    ) a where a.rn = 1
) d on a.in_from_code = d.in_from_code
   and a.out_to_code = d.out_to_code
   and a.in_from_area_code = d.in_from_area_code
--区县-城市
left join (
    select *
    from ( 
        select
            lastest.*
           ,row_number() over(partition by in_from_area_code,out_to_city_code order by search_type asc,total_time_use asc,in_edge_planned_departure desc,in_edge2_planned_departure desc,
                                           extra_in_planned_departure desc,e1_planned_departure desc,e2_planned_departure desc,e3_planned_departure desc,e4_planned_departure desc,
                                           e5_planned_departure desc,e6_planned_departure desc,out_edge_planned_departure desc,extra_out_planned_departure desc,out_edge2_planned_departure desc) as rn --区县最快
        from jms_tmp.dm_route_city_fastest_dt_tmp  lastest --兜底路由
        where dt = '{{ execution_date | cst_ds }}'
        and is_main_city_end = 1 
    ) a where a.rn = 1 
) e on a.in_from_code = e.in_from_code
   and a.out_to_code = e.out_to_code
   and a.in_from_area_code = e.in_from_area_code
   and a.out_to_city_code = e.out_to_city_code
--城市-网点
left join (
    select * 
    from ( 
        select
            lastest.*
           ,row_number() over(partition by in_from_city_code,out_to_code order by search_type asc,total_time_use asc,in_edge_planned_departure desc,in_edge2_planned_departure desc,
                                           extra_in_planned_departure desc,e1_planned_departure desc,e2_planned_departure desc,e3_planned_departure desc,e4_planned_departure desc,
                                           e5_planned_departure desc,e6_planned_departure desc,out_edge_planned_departure desc,extra_out_planned_departure desc,out_edge2_planned_departure desc) as rn --区县最快
        from jms_tmp.dm_route_city_fastest_dt_tmp lastest
        where dt = '{{ execution_date | cst_ds }}'
        and is_main_city_start = 1 
    ) a where a.rn = 1
) f on a.in_from_code = f.in_from_code
   and a.out_to_code = f.out_to_code
   and a.in_from_city_code = f.in_from_city_code
--城市-区县
left join (
    select *
    from ( 
        select
            lastest.*
           ,row_number() over(partition by in_from_city_code,out_to_area_code order by search_type asc,total_time_use asc,in_edge_planned_departure desc,in_edge2_planned_departure desc,
                                           extra_in_planned_departure desc,e1_planned_departure desc,e2_planned_departure desc,e3_planned_departure desc,e4_planned_departure desc,
                                           e5_planned_departure desc,e6_planned_departure desc,out_edge_planned_departure desc,extra_out_planned_departure desc,out_edge2_planned_departure desc) as rn --区县最快
        from jms_tmp.dm_route_city_fastest_dt_tmp  lastest --兜底路由
        where dt = '{{ execution_date | cst_ds }}'
        and is_main_city_start = 1 
    ) a where a.rn = 1 
) g on a.in_from_code = g.in_from_code
   and a.out_to_code = g.out_to_code
   and a.in_from_city_code = g.in_from_city_code
   and a.out_to_area_code = g.out_to_area_code
--城市-城市
left join (
    select *
    from ( 
        select
            lastest.*
           ,row_number() over(partition by in_from_city_code,out_to_city_code order by search_type asc,total_time_use asc,in_edge_planned_departure desc,in_edge2_planned_departure desc,
                                           extra_in_planned_departure desc,e1_planned_departure desc,e2_planned_departure desc,e3_planned_departure desc,e4_planned_departure desc,
                                           e5_planned_departure desc,e6_planned_departure desc,out_edge_planned_departure desc,extra_out_planned_departure desc,out_edge2_planned_departure desc) as rn --区县最快
        from jms_tmp.dm_route_city_fastest_dt_tmp lastest
        where dt = '{{ execution_date | cst_ds }}'
        and is_main_city = 1 
    ) a where a.rn = 1
) h on nvl(a.in_from_code,0) = nvl(h.in_from_code,0)
   and nvl(a.out_to_code ,0) = nvl(h.out_to_code,0)
   and a.in_from_city_code = h.in_from_city_code
   and a.out_to_city_code = h.out_to_city_code  
--区县-区县
left join (
    select *
    from ( 
        select
            lastest.*
           ,row_number() over(partition by in_from_area_code,out_to_area_code order by search_type asc,total_time_use asc,in_edge_planned_departure desc,in_edge2_planned_departure desc,
                                           extra_in_planned_departure desc,e1_planned_departure desc,e2_planned_departure desc,e3_planned_departure desc,e4_planned_departure desc,
                                           e5_planned_departure desc,e6_planned_departure desc,out_edge_planned_departure desc,extra_out_planned_departure desc,out_edge2_planned_departure desc) as rn --区县最快
        from jms_tmp.dm_route_city_fastest_dt_tmp  lastest --兜底路由
        where dt = '{{ execution_date | cst_ds }}'
    ) a where a.rn = 1 
) i on nvl(a.in_from_code,0) = nvl(i.in_from_code,0)
   and nvl(a.out_to_code ,0) = nvl(i.out_to_code,0)
   and a.in_from_area_code = i.in_from_area_code
   and a.out_to_area_code = i.out_to_area_code
distribute by pmod(hash(rand()),80);



